from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine(url='mysql+pymysql://root:123!#Qw@localhost/press?charset=utf8', echo=False)
session = sessionmaker(bind=engine)()
Base = declarative_base()


class Author(Base):
    """
    一类
    """
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
    article = relationship('Article', backref='author')

    def __repr__(self):
        return f'作者：{self.name},著作：{self.article}'


class Article(Base):
    """
    多类：一个作者可以写多篇文章
    """
    __tablename__ = 'articles'
    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False, index=True)
    content = Column(Text)
    author_id = Column(Integer, ForeignKey('author.id'))

    def __repr__(self):
        return f'书名：{self.title}，内容：{self.content},作者：{self.author.name}'


Base.metadata.create_all(engine)

# session.add_all([
    # Author(name='张三', password='123', email='342656@qq.com'),
    # Author(name='李四', password='wes', email='562656@qq.com'),
    # Article(title='三国演员', content='这是著名的小说1', author_id=1),
    # Article(title='三国道具', content='这是著名的小说2', author_id=1),
    # Article(title='四海追梦', content='这是个人小说', author_id=2),
    # Article(title='四海流浪记', content='这是另外的个人小说', author_id=2),
# ])
# 查询
author_one = session.query(Author).filter(Author.id == 1).first()
print(author_one)
print(author_one.article)
session.commit()
